create database [DAQLATM];
use [DAQLATM];
create table KhachHang(
	MaKhachHang varchar(15) not null,
	HoTen nvarchar(25),
	NgaySinh datetime, 
	GioiTinh bit,
	DiaChi nvarchar(50),
	SoCMND varchar(12),
	SDT varchar(15),
	DanToc nvarchar(25),
	QuocTich nvarchar(30),
	
);


create table TaiKhoan(
	SoTaiKhoan varchar(12) not null,
	MaKhachHang varchar(15),
	NgayTao datetime,
	NgayKichHoat datetime,
	SoDu bigint
);
--drop table TaiKhoan;
create table The(
	MaThe varchar(12) not null,
	SoTaiKhoan varchar(12),
	MaPin varchar(4),
	LoaiThe varchar(15)

);
--drop table The;
create table LoaiThe(
	MaLoaiThe varchar(15) not null,
	TenLoaiThe varchar(30)
);

create table LoaiGD(
	MaLoaiGD varchar(10) not null,
	TenGD nvarchar(25)
);

create table GiaoDich(
	MaGD bigint identity not null,
	MaThe varchar(12),
	SoTien bigint check ((SoTien>=5) and (SoTien<=25000)),
	LoaiTien varchar(10),
	SoTaiKhoanDoiUng varchar(12),
	MaATM varchar(10),
	TrangThaiGD nvarchar(15),
	LoaiGD varchar(10)
);

create table ATM(
	MaATM varchar(10) not null,
	SoTienCon bigint,
	DiaDiem nvarchar(50)
);
--Bang ATM--
alter table ATM add constraint PK_ATM primary key(MaATM);

--Bang Loai GD--
alter table LoaiGD add constraint PK_LoaiGD primary key(MaLoaiGD);

--Bang Loai The--
alter table LoaiThe add constraint PK_LoaiThe primary key(MaLoaiThe);

--Bang Khach Hang--
alter table KhachHang add constraint PK_KhachHang primary key(MaKhachHang);

--Bang Tai Khoan--
alter table TaiKhoan add constraint PK_TaiKhoan primary key(SoTaiKhoan),
		constraint FK_KhachHang foreign key(MaKhachHang) references KhachHang(MaKhachHang);

--Bang The--
alter table The add constraint PK_The primary key(MaThe),
	constraint FK_TaiKhoan foreign key(SoTaiKhoan) references TaiKhoan(SoTaiKhoan),
	constraint FK_LoaiThe foreign key(LoaiThe) references LoaiThe(MaLoaiThe);

--bang Giao Dich--
alter table GiaoDich add constraint PK_GiaDich primary key(MaGD),
	constraint Fk_LoaiGD foreign key(LoaiGD) references LoaiGD(MaLoaiGD),
	constraint FK_The foreign key(MaThe) references The(MaThe),
	constraint FK_ATM foreign key(MaATM) references ATM(MaATM);

------------------------------------------------------------------

select * from KhachHang
select * from KhachHang


create proc search_KhachHang
@MKH nvarchar(20), @HT nvarchar(30), @SDT nvarchar(15), @DC nvarchar(50)
as select * from KhachHang where MaKhachHang like '%'+@MKH+'%' and HoTen like '%'+@HT+'%' and SDT like '%'+@SDT+'%'
and DiaChi like '%'+@DC+'%'


-- Them khach hang moi
CREATE proc [dbo].[sp_insert_KhachHang] 
@MaKhachHang varchar(15),@HoTen nvarchar(25),@NgaySinh datetime, @GioiTinh bit, @DiaChi nvarchar(50),@SoCMND varchar(12),
@SDT varchar(15),@DanToc nvarchar(25), @QuocTich nvarchar(30),
@SoTaiKhoan varchar(20),@NgayTao datetime, 
@MaThe varchar(15), @MaPin varchar(6), @LoaiThe varchar(15)
as
insert into KhachHang values(@MaKhachHang,@HoTen,@NgaySinh,@GioiTinh,@DiaChi,@SoCMND,@SDT,@DanToc, @QuocTich);
insert into TaiKhoan values(@SoTaiKhoan, @MaKhachHang, @NgayTao,'',0);
insert into The values(@MaThe, @SoTaiKhoan, @MaPin, @MaKhachHang, @LoaiThe)

alter proc sp_insert_KhachHang
@MaKhachHang varchar(15),@HoTen nvarchar(25),@NgaySinh datetime, @GioiTinh bit, @DiaChi nvarchar(50),@SoCMND varchar(12),
@SDT varchar(15),@DanToc nvarchar(25), @QuocTich nvarchar(30),
@SoTaiKhoan varchar(20),@NgayTao datetime, 
@MaThe varchar(15), @MaPin varchar(6), @LoaiThe varchar(15)
as
if(@MaKhachHang not in (select MaKhachHang from KhachHang) or @SoTaiKhoan not in (select SoTaiKhoan from TaiKhoan) or @MaThe not in (Select MaThe from The))
begin
insert into KhachHang values(@MaKhachHang,@HoTen,@NgaySinh,@GioiTinh,@DiaChi,@SoCMND,@SDT,@DanToc, @QuocTich);
insert into TaiKhoan values(@SoTaiKhoan, @MaKhachHang, @NgayTao,'',0);
insert into The values(@MaThe, @SoTaiKhoan, @MaPin, @MaKhachHang, @LoaiThe)
end


Create table Admin(
	HoTen nvarchar(25),
	TenDangNhap varchar(20) primary key,
	MatKhau varchar(20),
)
